import pymysql
import time, datetime
from sqlite3 import Cursor

# 连接数据库
mysql=pymysql.connect(
	host='外网地址',
	port=23445,
	user='数据库账号',
	password='数据库密码',
	database='studentApprise'
)
cursor=mysql.cursor()

def getTeacherMessage():
	cursor.execute("SELECT * FROM teacherMessage")
	rep=cursor.fetchall()
	return rep
'''主界面，获取老师信息'''

def getStudentMessage(teacherName,courseTime):
	teacherName="'%s'" % teacherName
	courseTime="'%s'" % courseTime
	cursor.execute("SELECT * FROM studentMessage WHERE 任课教师 = " + teacherName + " AND 时间段 = " + courseTime)
	rep=cursor.fetchall()
	return rep
'''主界面，根据老师和时间段获取学生信息'''

def getCourseMessage_courseLevel(courseLevel):
	courseLevel="'%s'" % courseLevel
	cursor.execute("SELECT * FROM courseMessage WHERE 阶段 = " + courseLevel)
	rep=cursor.fetchall()
	return rep
'''主界面，根据阶段获取课程'''

def getQuestion(flowPath):
	flowPath="'%s'" % flowPath
	cursor.execute("SELECT * FROM question WHERE 流程 = " + flowPath)
	rep=cursor.fetchall()
	return rep
'''分界面，根据流程获取问题'''

def getAllQuestion():
	cursor.execute("SELECT * FROM question")
	rep=cursor.fetchall()
	return rep
'''分界面，获取所有问题'''

def getCourseMessage_courseName(courseName):
	courseName="'%s'" % courseName
	cursor.execute("SELECT * FROM courseMessage WHERE 课程 = " + courseName)
	rep=cursor.fetchall()
	return rep
'''分界面，根据课程名获取课程'''

def inAppriseLog(studentName,courseDate,courseName,courseTeacher,proxyTeacher,AttendanceStatus,appriseLog):
	appriseDate=str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
	query="INSERT INTO `appriseLog` (`存储日期`, `学生姓名`, `上课日期`, `上课课程`, `任课教师`, `代课教师`, `出勤状态`, `记录`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}');".format(appriseDate, studentName, courseDate, courseName, courseTeacher, proxyTeacher, AttendanceStatus, appriseLog)
	cursor.execute(query)
	mysql.commit()

def getAppriseLog_studentName(studentName):
	studentName="'%s'" % studentName
	cursor.execute("SELECT * FROM appriseLog WHERE 学生姓名 = " + studentName)
	rep=cursor.fetchall()
	return rep
'''分界面，根据学生获取日志'''

def getAppriseLog_studentName_courseName(studentName,courseName):
	studentName="'%s'" % studentName
	courseName="'%s'" % courseName
	cursor.execute("SELECT * FROM appriseLog WHERE 学生姓名 = " + studentName + " AND 上课课程 = " + courseName)
	rep=cursor.fetchall()
	return rep
'''主界面，根据学生获取日志'''

if __name__ == '__main__':
	# 测试
	lst=getAppriseLog_studentName('xxx')[-1][8]